clear 
set more off

global root "/Users/elizabethpisani/Dropbox/indon_uhc/"

global master "$root/master_data"
global tempfiles "$root/tempfiles"
global results "$root/results"
global dofiles "$root/do_files"

cd $root

run "$dofiles/name_indon_provinces.do"

************************
*SECTION A
************************

*IF WORKING SUSENAS FILE GETS CORRUPTED, RERUN SECTION A

use "$master/2018_Ind_new.dta", clear

*rename *inflation* *cpi*
save "$tempfiles/susenas_2018.dta", replace

*DROPPING VARIABLES WE ARE UNLIKELY TO USE IN THIS CODING OR ANALYSIS
drop treat_self treat_ip_rsu treat_ip_puskesmas treat_ip_private ///
treat_ip_other treat_ip_multiple refused_ip_paperwork refused_ip_notscreened ///
refused_ip_notcovered refused_ip_nostaff refused_ip_noroom refused_ip_nomeds ///
refused_ip_late refused_ip refused_hc_paperwork refused_hc_notscreened ///
refused_hc_notcovered refused_hc_nostaff refused_hc_nomeds refused_hc_late refused_hc ///
nu_hc_notsick nu_hc_selftreat nu_hc_complicated nu_hc_inactive nu_hc_toofar nu_hc_unstaffed ///
nu_hc_nomoney nu_hc_longwait nu_hc_otherinsurance nu_hc_multiple nu_ip_complicated ///
nu_ip_inactive nu_ip_toofar nu_ip_unstaffed nu_ip_nomoney nu_ip_longwait ///
nu_ip_otherinsurance nu_ip_multiple *food* rsp* 

ren district kabu_4dig
ren kabu district

gen region = .
replace region = 1 if province >=30 & province <=51
replace region = 2 if province <30
replace region = 3 if (province >51 & province <80) & province !=53
replace region = 4 if (province == 53 | province >=80)
lab var region "region of Indonesia"
lab def region 1 "Java and Bali" 2 "Sumatera & islands" 3 "NTB, Kalimantan, Sulawesi" 4 "NTT, Malukus, Papuas"
lab val region region 


*CALCULATE IMPOVERISHING HEALTH SPENDING 

lab var exp_cap "per capita expenditure per month, cacluated from household spending"
format exp_cap %9.0fc

gen anyoop = .
replace anyoop = 0 if health_exp_1yr_hh ==0 | health_exp_1yr_hh ==.
replace anyoop = 1 if health_exp_1yr_hh >0 & health_exp_1yr_hh !=.
lab var anyoop "household has spent something on health in last 3 months"
lab val anyoop yesno

gen treated_op = .
replace treated_op = 0 if symptoms_1mo == 1 & outpatient_1mo == 0
replace treated_op = 1 if symptoms_1mo == 1 & outpatient_1mo == 1
lab var treated_op "sought outpatient treatment last month, of those who felt sick 

gen treat_any = inpatient_1yr + outpatient_1mo
recode treat_any 2=1
lab var treat_any "reported any formal service use (inpatient 1yr, outpatient 1mo)"

gen poor = .
replace poor = 1 if exp_nonhealth_hh_cap< poverty_line
replace poor = 0 if exp_nonhealth_hh_cap>= poverty_line
lab var poor "monthly hn non-health expenditure is below local poverty line"
lab val poor yesno

gen impoverish = .
replace impoverish = 0 if anyoop == 0 | (anyoop==1 & (exp_nonhealth_hh_cap-health_exp_ave1mo_cap >poverty_line)) 
replace impoverish = 1 if (anyoop == 1 & poor==1) | (anyoop == 1 & (exp_nonhealth_hh_cap - health_exp_ave1mo_cap <=poverty_line))
lab var impoverish "health spending creates or deepens poverty (notes)"
notes impoverish: "includes families already below poverty line if health spending deepends poverty; excludes already poor families if they report no oop health spending"

*CATEGORISE OOP ON INPATIENT AS PROPORTION OF HOUSEHOLD SPENDING EXCLUDING HEALTH
*THIS GIVES A PICTURE OF COMPARATIVE WEALTH OF HOUSEHOLDS, WITHOUT COUNTING HEALTH SPENDING AS "WEALTH"
gen cat_inpatient_oop_nonhealth = .
replace cat_inpatient_oop_nonhealth = 0 if inpatient_1yr == 1 & anyoop_inpatient==0
replace cat_inpatient_oop_nonhealth = 1 if inpatient_1yr == 1 & anyoop_inpatient==1 & (inpatient_ave1mo_realcap < 0.1*exp_nonhealth_hh)
replace cat_inpatient_oop_nonhealth = 2 if inpatient_1yr == 1 & anyoop_inpatient==1 & (inpatient_ave1mo_realcap >= 0.1*exp_nonhealth_hh)
lab var cat_inpatient_oop_nonhealth "Mean per capita hh spending on inpatient care, for those with any inpatient care last 12 months" 
lab def cat_inpatient_oop_nonhealth 0 "None" 1 "< 10% percapita hh spending, excluding health" 2 ">= 10% percapita hh spending, excluding health"
lab val cat_inpatient_oop_nonhealth cat_inpatient_oop_nonhealth 

gen citystr= string(district)
gen city=0 
replace city = 1 if substr(citystr,3,1) == "7"
drop citystr
lab var city "kota, not kabupaten"
lab def city 0 "Rural" 1 "City"
lab val city city


*CONVERT TO US$ 
local expenditure health_exp_1yr_hh health_exp_1yr_hh_cap health_exp_1yr_private inpatient_ave1yr_realcap inpatient_ave1yr_cap_all outpatient_ave1mo outpatient_ave1mo_cap
foreach x of local expenditure {
	gen usd_`x' = (`x'/13760)
	local lbl : var label `x'
	lab var usd_`x'  "US$ `lbl', at March 2018 exchange rate'"
}

format usd_* %15.2fc
save "analysis_data/2018/hh/susenas_2018.dta", replace


*/
************************************
*SECTION B:
*MAKE DATASET OF REGIONAL POPULATIONS FOR BPJS REAL PERCAPITA COMPARISONS
************************************
*HERMA PLEASE CHECK THIS SECTION. WE WERE USING WEIND BUT GAVE WRONG TOTALS WITH NEW DATASET
*I HAVE CHANGED TO WERT AND IT LOOKS BETTER...
use "analysis_data/2018/hh/susenas_2018.dta", clear

gen population = wert
gen insured_total = insurance*wert
gen insured_jkn_total = insurance_jkn_all*wert
format population insured_total insured_jkn_total %15.0fc 
lab var insured_total "total number of people reporting insurance, calculated with sampling weights"
lab var insured_jkn_total "total number of people reporting jkn/jamkesda insurance, calculated with sampling weights"
lab var population "total population, for real percapita calculations"
save "analysis_data/2018/hh/popdata.dta", replace
use "analysis_data/2018/hh/popdata.dta", clear
collapse (sum) population insured_total insured_jkn_total, by (district year)
lab var insured_total "total number of people reporting insurance, calculated with sampling weights"
lab var insured_jkn_total "total number of people reporting jkn/jamkesda insurance, calculated with sampling weights"
lab var population "total population, for real percapita calculations"
save "analysis_data/2018/district/popdata_kab.dta", replace
use "analysis_data/2018/hh/popdata.dta", clear
collapse (sum) population insured_total insured_jkn_total, by (province year)
lab var insured_total "total number of people reporting insurance, calculated with sampling weights"
lab var insured_jkn_total "total number of people reporting jkn/jamkesda insurance, calculated with sampling weights"
lab var population "total population, for real percapita calculations"
save "analysis_data/2018/province/popdata_prov.dta", replace
use "analysis_data/2018/hh/popdata.dta", clear
collapse (sum) population insured_total insured_jkn_total, by (region year)
lab var insured_total "total number of people reporting insurance, calculated with sampling weights"
lab var insured_jkn_total "total number of people reporting jkn/jamkesda insurance, calculated with sampling weights"
lab var population "total population, for real percapita calculations"
save "analysis_data/2018/region/popdata_reg.dta", replace



************************
*SECTION C:
*CODE PODES DATA
************************
/*
*IF PODES 2018 GETS CORRUPTED, RERUN THE FOLLOWING
*IMPORT 2018 DATA AND RECODE TO MATCH 2014

clear
import dbase $master/podes2018_desa_part2_revisi.dbf, case(lower)
destring r7*, replace
rename r70*k* r70*_k*
keep r101* r102* r103* r104* r704a_k2-r711b
gen year =2018
*MAKING VARIABLES MATCH THOSE IN PODES 2014, WHICH HAD A FILTER QUESTION
ren r704*_k4 r704*_k5
ren r704*_k3 r704*_k4
ren r704*_k2 r704*_k3

foreach var of varlist r704*_k3 {
generate `var'_newk2 = `var' 
recode `var'_newk2 1/max =1
}
rename r704*_k3_newk2 r704*_k2
rename r704h* rumahbersalin*
rename r704i* bidanpraktek*
rename r704j* poskesdes*
rename r704k* polindes*
rename bidanpraktek* r704h*
rename poskesdes* r704i*
rename polindes* r704j*

save "$master/podes_2018.dta", replace
*/


use "$master/podes_2018.dta", clear
save "analysis_data/2018/hh/podesanalysis.dta", replace
save"$tempfiles/podesanalysis_temp.dta", replace

*GEOGRAPHIC VARIABLES


gen province = r101
gen district = r101+r102
gen subdistrict = district+r103
gen village = subdistrict+r104
destring province district, replace

nameprovs


*DEFINE LABELS AND CODE HEALTH SERVICE ACCESS VARIABLES
lab def yesno 1 "yes" 0 "no"
lab def easyaccess 1 "very easy" 2 "easy" 3 "difficult" 4 "very difficult"

gen hospital=r704a_k2
recode hospital 2=0 
lab var hospital "hospital in village"
lab val hospital yesno
gen hospitalaccessdet=r704a_k5
lab var hospitalaccessdet "access to nearest hospital, detail" 
lab val hospitalaccessdet easyaccess
gen hospitalaccess=.
replace hospitalaccess=0 if hospitalaccessdet==3 | hospitalaccessdet==4
replace hospitalaccess=1 if hospital==1 | hospitalaccessdet==1 | hospitalaccessdet==2
lab var hospitalaccess "easy access to hospital"
lab val hospitalaccess yesno
gen podes_hospitalaccess= hospitalaccess
gen podes_nohospitalaccess= hospitalaccess
recode podes_nohospitalaccess 0=1 1=0
lab var podes_hospitalaccess "village has easy access to hospital"
lab var podes_nohospitalaccess "village does not have access to hospital"
gen total_hospital = r704a_k3
replace total_hospital = 0 if total_hospital == 0| hospital == 0
lab var total_hospital "total number of hospitals"

gen maternity=r704b_k2
recode maternity 2=0 
lab var maternity "maternity hospital in village"
lab val maternity yesno
gen maternityaccessdet=r704b_k5
lab var maternityaccessdet "access to nearest maternity hospital, detail" 
lab val maternityaccessdet easyaccess
gen maternityaccess=.
replace maternityaccess=0 if maternityaccessdet==3 | maternityaccessdet==4
replace maternityaccess=1 if maternity==1 | maternityaccessdet==1 | maternityaccessdet==2
lab var maternityaccess "easy access to maternity"
lab val maternityaccess yesno
gen total_maternity = r704b_k3
replace total_maternity = 0 if total_maternity == 0| maternity == 0
lab var total_maternity "total number of maternity hospitals"

gen maternitywaiting=rumahbersalin_k2
recode maternitywaiting 2=0 
lab var maternitywaiting "maternity waiting house in village"
lab val maternitywaiting yesno
gen maternitywaitingaccessdet=rumahbersalin_k5
lab var maternitywaitingaccessdet "access to nearest maternity waiting house, detail" 
lab val maternitywaitingaccessdet easyaccess
gen maternitywaitingaccess=.
replace maternitywaitingaccess=0 if maternitywaitingaccessdet==3 | maternitywaitingaccessdet==4
replace maternitywaitingaccess=1 if maternitywaiting==1 | maternitywaitingaccessdet==1 | maternitywaitingaccessdet==2
lab var maternitywaitingaccess "easy access to maternity waiting house"
lab val maternitywaitingaccess yesno
gen total_maternitywaiting = rumahbersalin_k3
replace total_maternitywaiting = 0 if total_maternitywaiting == 0| maternitywaiting == 0
lab var total_maternitywaiting "total number of maternity waiting houses"


gen phcinpatient=r704c_k2
recode phcinpatient 2=0 
lab var phcinpatient "phc with inpatient in village"
lab val phcinpatient yesno
gen phcinpatientaccessdet=r704c_k5
lab var phcinpatientaccessdet "access to nearest phc with inpatient, detail" 
lab val phcinpatientaccessdet easyaccess
gen phcinpatientaccess=.
replace phcinpatientaccess=0 if phcinpatientaccessdet==3 | phcinpatientaccessdet==4
replace phcinpatientaccess=1 if phcinpatient==1 | phcinpatientaccessdet==1 | phcinpatientaccessdet==2
lab var phcinpatientaccess "easy access to phc with inpatient"
lab val phcinpatientaccess yesno
gen total_phcinpatient = r704c_k3
replace total_phcinpatient = 0 if total_phcinpatient == 0| phcinpatient == 0
lab var total_phcinpatient "total number of phc with inpatient


gen phcoutpatient=r704d_k2
recode phcoutpatient 2=0 
lab var phcoutpatient "outpatient-only phc in village"
lab val phcoutpatient yesno
gen phcoutpatientaccessdet=r704d_k5
lab var phcoutpatientaccessdet "access to nearest outpatient phc, detail" 
lab val phcoutpatientaccessdet easyaccess
gen phcoutpatientaccess=.
replace phcoutpatientaccess=0 if phcoutpatientaccessdet==3 | phcoutpatientaccessdet==4
replace phcoutpatientaccess=1 if phcoutpatient==1 | phcoutpatientaccessdet==1 | phcoutpatientaccessdet==2
lab var phcoutpatientaccess "easy access to outpatient phc"
lab val phcoutpatientaccess yesno
gen total_phcoutpatient = r704d_k3
replace total_phcoutpatient = 0 if total_phcoutpatient == 0| phcoutpatient == 0
lab var total_phcoutpatient "total number of outpatient-only phc"
gen podes_puskesmas_access = phcoutpatientaccess
lab var podes_puskesmas_access "easy access to outpatient primary heatlh centre"

gen clinic=r704f_k2
recode clinic 2=0 
lab var clinic "clinic in village"
lab val clinic yesno
gen clinicaccessdet=r704f_k5
lab var clinicaccessdet "access to nearest clinic, detail" 
lab val clinicaccessdet easyaccess
gen clinicaccess=.
replace clinicaccess=0 if clinicaccessdet==3 | clinicaccessdet==4
replace clinicaccess=1 if clinic==1 | clinicaccessdet==1 | clinicaccessdet==2
lab var clinicaccess "easy access to clinic"
lab val clinicaccess yesno
gen total_clinic = r704f_k3
replace total_clinic = 0 if total_clinic == 0| clinic == 0
lab var total_clinic "total number of clinics"

gen privatedoctor=r704g_k2
recode privatedoctor 2=0 
lab var privatedoctor "private practice doctor in village"
lab val privatedoctor yesno
gen privatedoctoraccessdet=r704g_k5
lab var privatedoctoraccessdet "access to nearest private practice doctor, detail" 
lab val privatedoctoraccessdet easyaccess
gen privatedoctoraccess=.
replace privatedoctoraccess=0 if privatedoctoraccessdet==3 | privatedoctoraccessdet==4
replace privatedoctoraccess=1 if privatedoctor==1 | privatedoctoraccessdet==1 | privatedoctoraccessdet==2
lab var privatedoctoraccess "easy access to private practice doctor"
lab val privatedoctoraccess yesno
gen total_privatedoctor = r704g_k3
replace total_privatedoctor = 0 if total_privatedoctor == 0| privatedoctor == 0
lab var total_privatedoctor "total number of private doctors"

gen privatemidwife=r704h_k2
recode privatemidwife 2=0 
lab var privatemidwife "private practice midwife in village"
lab val privatemidwife yesno
gen privatemidwifeaccessdet=r704h_k5
lab var privatemidwifeaccessdet "access to nearest private practice midwife, detail" 
lab val privatemidwifeaccessdet easyaccess
gen privatemidwifeaccess=.
replace privatemidwifeaccess=0 if privatemidwifeaccessdet==3 | privatemidwifeaccessdet==4
replace privatemidwifeaccess=1 if privatemidwife==1 | privatemidwifeaccessdet==1 | privatemidwifeaccessdet==2
lab var privatemidwifeaccess "easy access to private practice midwife"
lab val privatemidwifeaccess yesno
gen total_privatemidwife = r704h_k3
replace total_privatemidwife = 0 if total_privatemidwife == 0| privatemidwife == 0
lab var total_privatemidwife "total number of private midwifes"


gen pharmacy=r704l_k2
recode pharmacy 2=0 
lab var pharmacy "pharmacy in village"
lab val pharmacy yesno
gen pharmacyaccessdet=r704l_k5
lab var pharmacyaccessdet "access to nearest pharmacy, detail" 
lab val pharmacyaccessdet easyaccess
gen pharmacyaccess=.
replace pharmacyaccess=0 if pharmacyaccessdet==3 | pharmacyaccessdet==4
replace pharmacyaccess=1 if pharmacy==1 | pharmacyaccessdet==1 | pharmacyaccessdet==2
lab var pharmacyaccess "easy access to pharmacy"
lab val pharmacyaccess yesno
gen total_pharmacy = r704l_k3
replace total_pharmacy = 0 if total_pharmacy == 0| pharmacy == 0
lab var total_pharmacy "total number of pharmacies"

recode hospitalaccess maternityaccess maternitywaitingaccess phcinpatientaccess phcoutpatientaccess clinicaccess privatedoctoraccess privatemidwifeaccess pharmacyaccess (.=0), prefix(fortotal_)

gen totalaccess_pay=fortotal_hospitalaccess+fortotal_maternityaccess+fortotal_maternitywaitingaccess+fortotal_phcinpatientaccess+fortotal_clinicaccess+fortotal_privatedoctoraccess+fortotal_privatemidwifeaccess+fortotal_pharmacyaccess

lab var totalaccess_pay "total access to health service score (max.9)"

gen score_pay=.
replace score_pay=0 if totalaccess_pay==0
replace score_pay=1 if totalaccess_pay>=1 & totalaccess_pay <4
replace score_pay=2 if hospitalaccess == 1 | (hospitalaccess == 0 & (totalaccess_pay>=4 & totalaccess_pay !=.)) 

lab var score_pay "total score easy access to HC facilities where payment likely"
lab def score 0 "no access" 1 "limited" 2 "good access"
lab val score_pay score
note score_pay: "summary score of easy access to services: hospital, maternity hospital, materinity waiting house, phc_inpatient, clinic, private doctor, private midwife, pharmacy. Limited access=easy access to 1-3 facilites. good access= easy access to a hospital, or 4+ facilities. Outpatient-only puskesmas excluded because payment unlikely"

gen podes_pay_binary = score_pay
recode podes_pay_binary 1=0 2=1
lab var podes_pay_binary "village has good access to hospital or 4+ other health services where payment is likely"
lab val podes_pay_binary yesno

gen podes_zero_pay_binary = score_pay
recode podes_zero_pay_binary 0=1 1=0 2/max=0
lab var podes_zero_pay_binary "village has no access to any health service where payment is likely"
lab val podes_zero_pay_binary yesno

gen podes_inpatientaccess = fortotal_hospitalaccess+fortotal_maternityaccess+fortotal_phcinpatientaccess
replace podes_inpatientaccess = 1 if podes_inpatientaccess >=1 & podes_inpatientaccess !=0
lab var podes_inpatientaccess "easy access to hospital, maternity or inpatient puskesmas"
lab val podes_inpatientaccess yesno

gen podes_noinpatientaccess = podes_inpatientaccess
recode podes_noinpatientaccess 0=1 1=0
lab var podes_noinpatientaccess "no easy access to hospital, maternity or inpatient puskesmas"

gen podes_inpatient_primary_only = .
replace podes_inpatient_primary_only = 0 if podes_inpatientaccess == 0 | ((hospitalaccess == 1 | maternityaccess == 1) & phcinpatientaccess == 0) | (podes_inpatientaccess == 1 & phcinpatientaccess == 1)
replace podes_inpatient_primary_only = 1 if phcinpatientaccess == 1 & hospitalaccess == 0  
lab var podes_inpatient_primary_only "easy access inpatient puskesmas, but no hospital"


gen totalaccess_use = fortotal_hospitalaccess+fortotal_maternityaccess+fortotal_maternitywaitingaccess+fortotal_phcinpatientaccess+fortotal_phcoutpatient+fortotal_clinicaccess+fortotal_privatedoctoraccess+fortotal_privatemidwifeaccess

gen score_use=.
replace score_use=0 if totalaccess_use==0
replace score_use=1 if totalaccess_use>=1 & totalaccess_use <4
replace score_use=2 if hospitalaccess == 1 | (hospitalaccess==0 & (totalaccess_use>=4 & totalaccess_use !=.)) 

lab var score_use "total score easy access to HC facilities"
lab val score_use score
note score_use: "summary score of easy access to health services: hospital, maternity hospital, materinity waitinghouse any phc, clinic, private doctor, private midwife. Limited access=easy access to 1-3 facilites. good access= easy access to a hospital, or 4+ facilities."

gen podes_access_binary = score_use
recode podes_access_binary 1=0 2=1
lab var podes_access_binary "village has good access to hospital or 4+ other health services, incl primary"
lab val podes_access_binary yesno

gen podes_zero_access_binary = score_use
recode podes_zero_access_binary 0=1 1=0 2/max=0
lab var podes_zero_access_binary "village has no access to any health service, incl primary"
lab val podes_zero_access_binary yesno

gen region = .
replace region = 1 if province >=30 & province <=51
replace region = 2 if province <30
replace region = 3 if (province >51 & province <80) & province !=53
replace region = 4 if (province == 53 | province >=80)
lab var region "region of Indonesia"
lab def region 1 "Java and Bali" 2 "Sumatera & islands" 3 "NTB, Kalimantan, Sulawesi" 4 "NTT, Malukus, Papuas"
lab val region region 

*ADD SHORT PROVINCE NAMES FOR GRAPHICS AND REPORTING
provshort

*DATA FROM LATER PODES QUESTIONS ABOUT DOCTORS, HEALTH NEEDS AND INSURANCE

gen doctorsinvillage = r706a1+r706a2
lab var doctorsinvillage "number of doctors reported to be living in village"
gen anydoctorinvillage = doctorsinvillage 
recode anydoctorinvillage 1/max = 1
lab var doctorsinvillage "any doctor is living in village"

foreach var of varlist r709*_k2 {
recode `var' 2=0
}

gen outbreak = r709a_k2 + r709b_k2 + r709c_k2 +r709d_k2 +r709e_k2 +r709f_k2 +r709g_k2 +r709h_k2
recode outbreak 1/max = 1
lab var outbreak "village reports at least one infectious disease outbreak in past year"

format podes* %9.1f

save "analysis_data/2018/hh/podesanalysis.dta", replace

*******************************************


************************
*SECTION C0.1:
*MAKE SUBSETS OF TOTALS FOR LATER INTEGRATION. 
************************

use "analysis_data/2018/hh/podesanalysis.dta", clear
save "$tempfiles/podesmerge_kab_temp1.dta", replace

collapse (sum) total_*, by (year district)
rename total_* podes_total_*
save "$tempfiles/podesmerge_kab_total_faskes_temp.dta", replace

use "analysis_data/2018/hh/podesanalysis.dta", clear
collapse (sum) total_*, by (year province)
rename total_* podes_total_*
save "$tempfiles/podesmerge_prov_total_faskes_temp.dta", replace

use "analysis_data/2018/hh/podesanalysis.dta", clear
collapse (sum) total_*, by (year region)
rename total_* podes_total_*
save "$tempfiles/podesmerge_reg_total_faskes_temp.dta", replace


************************
*SECTION C1:
*MANAGE PODES DATA FOR MERGE WITH SUSENAS AT KABUPATEN LEVEL AND FOR MAPPING
************************

use "analysis_data/2018/hh/podesanalysis.dta", clear
save "$tempfiles/podesmerge_kab_temp1.dta", replace

collapse (mean) (podes_*), by (district year)
local podes_all podes_hospitalaccess podes_nohospitalaccess podes_pay_binary podes_zero_pay_binary ///
podes_inpatientaccess podes_noinpatientaccess podes_inpatient_primary_only podes_access_binary podes_zero_access_binary podes_puskesmas_access
foreach x of local podes_all {
replace `x' = (`x'*100)
}
lab var podes_hospitalaccess "pct villages w good access to hospital"
lab var podes_nohospitalaccess "pct villages w NO good access to hospital"
lab var podes_pay_binary "pct villages w good access to hospital or 4+ other health services where payment is likely"
lab var podes_zero_pay_binary "pct villages w access to any health service where payment is likely"
lab var podes_inpatientaccess "pct villages w easy access to hospital, maternity or inpatient puskesmas"
lab var podes_noinpatientaccess "pct villages w NO easy access to hospital, maternity or inpatient puskesmas"
lab var podes_access_binary "pct villages w good access to hospital or 4+ other health services, incl primary"
lab var podes_zero_access_binary "pct villages w no access to any health service, incl primary"
lab var podes_inpatient_primary_only "pct villages w good access to inpatient puskesmas but no hospital"
lab var podes_puskesmas_access "pct villages with easy access to outpatient primary heatlh centre"

*CATEGORISE INPATIENT AND HOSPITAL FACILITIES
gen cat_nohospital = .
replace cat_nohospital = 1 if podes_nohospitalaccess ==0
replace cat_nohospital = 2 if podes_nohospitalaccess >0 & podes_nohospitalaccess <=5
replace cat_nohospital = 3 if podes_nohospitalaccess >5 & podes_nohospitalaccess <=20
replace cat_nohospital = 4 if podes_nohospitalaccess >20 & podes_nohospitalaccess <=50
replace cat_nohospital = 5 if podes_nohospitalaccess >50 & podes_nohospitalaccess !=.
lab var cat_nohospital "cat % of villages in district with no easy access to a hospital"
lab def cat_nohospital 1 "0" 2 "up to 5%" 3 ">5 to 20%" 4 ">20 to 50%" 5 ">50%"
lab val cat_nohospital cat_nohospital

gen cat_noinpatient = .
replace cat_noinpatient = 1 if podes_noinpatientaccess ==0
replace cat_noinpatient = 2 if podes_noinpatientaccess >0 & podes_noinpatientaccess <=5
replace cat_noinpatient = 3 if podes_noinpatientaccess >5 & podes_noinpatientaccess <=20
replace cat_noinpatient = 4 if podes_noinpatientaccess >20 & podes_noinpatientaccess <=50
replace cat_noinpatient = 5 if podes_noinpatientaccess >50 & podes_noinpatientaccess !=.
lab var cat_noinpatient "cat % of villages in district with no easy access to inpatient facilities"
lab def cat_noinpatient 1 "0" 2 "up to 5%" 3 ">5 to 20%" 4 ">20 to 50%" 5 ">50%"
lab val cat_noinpatient cat_nohospital

gen cat_pctgood_use = .
replace cat_pctgood_use = 1 if podes_access_binary >0 & podes_access_binary <=50
replace cat_pctgood_use = 2 if podes_access_binary >50 & podes_access_binary <=70
replace cat_pctgood_use = 3 if podes_access_binary >70 & podes_access_binary <=95
replace cat_pctgood_use = 4 if podes_access_binary >95 & podes_access_binary <100
replace cat_pctgood_use = 5 if podes_access_binary ==100 & podes_access_binary !=.
lab var cat_pctgood_use "cat % of villages in district with access to hospital or 4+ health services"
lab def cat_pctgood 1 "<=50%" 2 ">50% to 70%" 3 ">70 to 95%" 4 ">95 to <100%" 5 "100%"
lab val cat_pctgood_use cat_pctgood

gen cat_pctgood_pay = .
replace cat_pctgood_pay = 1 if podes_pay_binary >0 & podes_pay_binary <=50
replace cat_pctgood_pay = 2 if podes_pay_binary >50 & podes_pay_binary <=70
replace cat_pctgood_pay = 3 if podes_pay_binary >70 & podes_pay_binary <=95
replace cat_pctgood_pay = 4 if podes_pay_binary >95 & podes_pay_binary <100
replace cat_pctgood_pay = 5 if podes_pay_binary ==100 & podes_pay_binary !=.
lab var cat_pctgood_pay "cat % of villages in district w/ access to hospital or 4+ health services where payment is likely"
lab val cat_pctgood_pay cat_pctgood

gen cat_pctzero_use = .
replace cat_pctzero_use = 1 if podes_zero_access_binary ==0
replace cat_pctzero_use = 2 if podes_zero_access_binary >0 & podes_zero_access_binary <=5
replace cat_pctzero_use = 3 if podes_zero_access_binary >5 & podes_zero_access_binary <=20
replace cat_pctzero_use = 4 if podes_zero_access_binary >20 & podes_zero_access_binary <=50
replace cat_pctzero_use = 5 if podes_zero_access_binary >50 & podes_zero_access_binary !=.
lab var cat_pctzero_use "cat % of villages in district with no easy access to any health services"
lab val cat_pctzero_use cat_nohospital

save "$tempfiles/podesmerge_kab_temp2.dta", replace

*NOW MERGE IN TOTAL NUMBERS OF HEALTH FACILITIES
merge 1:1 district year using "$tempfiles/podesmerge_kab_total_faskes_temp.dta"
drop _merge
format podes* %9.1f

save "analysis_data/2018/district/podes_kab.dta", replace
*USE THIS FILE FOR STANDALONE ANALYSIS OF PODES AT KABUPATEN LEVEL 


******************
*SECTION C2:
*PODES FOR PROVINCIAL MERGE AND MAPPING
******************

use "analysis_data/2018/hh/podesanalysis.dta", clear
save "$tempfiles/podesmerge_prov_temp1.dta", replace
collapse (mean) (podes_*), by (province year)
local podes_all podes_hospitalaccess podes_nohospitalaccess podes_pay_binary podes_zero_pay_binary ///
podes_inpatientaccess podes_noinpatientaccess podes_inpatient_primary_only podes_access_binary podes_zero_access_binary podes_puskesmas_access
foreach x of local podes_all {
replace `x' = (`x'*100)
}
lab var podes_hospitalaccess "pct villages w good access to hospital"
lab var podes_nohospitalaccess "pct villages w NO good access to hospital"
lab var podes_pay_binary "pct villages w good access to hospital or 4+ other health services where payment is likely"
lab var podes_zero_pay_binary "pct villages w access to any health service where payment is likely"
lab var podes_inpatientaccess "pct villages w easy access to hospital, maternity or inpatient puskesmas"
lab var podes_noinpatientaccess "pct villages w NO easy access to hospital, maternity or inpatient puskesmas"
lab var podes_access_binary "pct villages w good access to hospital or 4+ other health services, incl primary"
lab var podes_zero_access_binary "pct villages w no access to any health service, incl primary"
lab var podes_inpatient_primary_only "pct villages w good access to inpatient puskesmas but no hospital"
lab var podes_puskesmas_access "pct villages w easy access to outpatient primary heatlh centre"

*CATEGORISE INPATIENT AND HOSPITAL FACILITIES
gen cat_nohospital = .
replace cat_nohospital = 1 if podes_nohospitalaccess ==0
replace cat_nohospital = 2 if podes_nohospitalaccess >0 & podes_nohospitalaccess <=5
replace cat_nohospital = 3 if podes_nohospitalaccess >5 & podes_nohospitalaccess <=20
replace cat_nohospital = 4 if podes_nohospitalaccess >20 & podes_nohospitalaccess <=50
replace cat_nohospital = 5 if podes_nohospitalaccess >50 & podes_nohospitalaccess !=.
lab var cat_nohospital "cat % of villages in province with no easy access to a hospital"
lab def cat_nohospital 1 "0" 2 "up to 5%" 3 ">5 to 20%" 4 ">20 to 50%" 5 ">50%"
lab val cat_nohospital cat_nohospital

gen cat_noinpatient = .
replace cat_noinpatient = 1 if podes_noinpatientaccess ==0
replace cat_noinpatient = 2 if podes_noinpatientaccess >0 & podes_noinpatientaccess <=5
replace cat_noinpatient = 3 if podes_noinpatientaccess >5 & podes_noinpatientaccess <=20
replace cat_noinpatient = 4 if podes_noinpatientaccess >20 & podes_noinpatientaccess <=50
replace cat_noinpatient = 5 if podes_noinpatientaccess >50 & podes_noinpatientaccess !=.
lab var cat_noinpatient "cat % of villages in province with no easy access to inpatient facilities"
lab def cat_noinpatient 1 "0" 2 "up to 5%" 3 ">5 to 20%" 4 ">20 to 50%" 5 ">50%"
lab val cat_noinpatient cat_nohospital

gen cat_pctgood_use = .
replace cat_pctgood_use = 1 if podes_access_binary >0 & podes_access_binary <=50
replace cat_pctgood_use = 2 if podes_access_binary >50 & podes_access_binary <=70
replace cat_pctgood_use = 3 if podes_access_binary >70 & podes_access_binary <=95
replace cat_pctgood_use = 4 if podes_access_binary >95 & podes_access_binary <100
replace cat_pctgood_use = 5 if podes_access_binary ==100 & podes_access_binary !=.
lab var cat_pctgood_use "cat % of villages in province with access to hospital or 4+ health services"
lab def cat_pctgood 1 "<=50%" 2 ">50% to 70%" 3 ">70 to 95%" 4 ">95 to <100%" 5 "100%"
lab val cat_pctgood_use cat_pctgood

gen cat_pctgood_pay = .
replace cat_pctgood_pay = 1 if podes_pay_binary >0 & podes_pay_binary <=50
replace cat_pctgood_pay = 2 if podes_pay_binary >50 & podes_pay_binary <=70
replace cat_pctgood_pay = 3 if podes_pay_binary >70 & podes_pay_binary <=95
replace cat_pctgood_pay = 4 if podes_pay_binary >95 & podes_pay_binary <100
replace cat_pctgood_pay = 5 if podes_pay_binary ==100 & podes_pay_binary !=.
lab var cat_pctgood_pay "cat % of villages in province w/ access to hospital or 4+ health services where payment is likely"
lab val cat_pctgood_pay cat_pctgood

gen cat_pctzero_use = .
replace cat_pctzero_use = 1 if podes_zero_access_binary ==0
replace cat_pctzero_use = 2 if podes_zero_access_binary >0 & podes_zero_access_binary <=5
replace cat_pctzero_use = 3 if podes_zero_access_binary >5 & podes_zero_access_binary <=20
replace cat_pctzero_use = 4 if podes_zero_access_binary >20 & podes_zero_access_binary <=50
replace cat_pctzero_use = 5 if podes_zero_access_binary >50 & podes_zero_access_binary !=.
lab var cat_pctzero_use "cat % of villages in province with no easy access to any health services"
lab val cat_pctzero_use cat_nohospital

format podes* %9.1f


*NOW MERGE IN TOTAL NUMBERS OF HEALTH FACILITIES
merge 1:1 province year using "$tempfiles/podesmerge_prov_total_faskes_temp.dta"
drop _merge
save "analysis_data/2018/province/podes_prov.dta", replace


******************
*SECTION C3:
*PODES FOR REGIONAL MERGE AND MAPPING
******************

use "analysis_data/2018/hh/podesanalysis.dta", clear
save "$tempfiles/podesmerge_reg_temp1.dta", replace
collapse (mean) (podes_*), by (region year)
local podes_all podes_hospitalaccess podes_nohospitalaccess podes_pay_binary podes_zero_pay_binary ///
podes_inpatientaccess podes_noinpatientaccess podes_inpatient_primary_only podes_access_binary podes_zero_access_binary podes_puskesmas_access
foreach x of local podes_all {
replace `x' = (`x'*100)
}
lab var podes_hospitalaccess "pct villages w good access to hospital"
lab var podes_nohospitalaccess "pct villages w NO good access to hospital"
lab var podes_pay_binary "pct villages w good access to hospital or 4+ other health services where payment is likely"
lab var podes_zero_pay_binary "pct villages w access to any health service where payment is likely"
lab var podes_inpatientaccess "pct villages w easy access to hospital, maternity or inpatient puskesmas"
lab var podes_noinpatientaccess "pct villages w NO easy access to hospital, maternity or inpatient puskesmas"
lab var podes_access_binary "pct villages w good access to hospital or 4+ other health services, incl primary"
lab var podes_zero_access_binary "pct villages w no access to any health service, incl primary"
lab var podes_inpatient_primary_only "pct villages w good access to inpatient puskesmas but no hospital"
lab var podes_puskesmas_access "pct villages w easy access to outpatient primary heatlh centre"

*CATEGORISE INPATIENT AND HOSPITAL FACILITIES
gen cat_nohospital = .
replace cat_nohospital = 1 if podes_nohospitalaccess ==0
replace cat_nohospital = 2 if podes_nohospitalaccess >0 & podes_nohospitalaccess <=5
replace cat_nohospital = 3 if podes_nohospitalaccess >5 & podes_nohospitalaccess <=20
replace cat_nohospital = 4 if podes_nohospitalaccess >20 & podes_nohospitalaccess <=50
replace cat_nohospital = 5 if podes_nohospitalaccess >50 & podes_nohospitalaccess !=.
lab var cat_nohospital "cat % of villages in region with no easy access to a hospital"
lab def cat_nohospital 1 "0" 2 "up to 5%" 3 ">5 to 20%" 4 ">20 to 50%" 5 ">50%"
lab val cat_nohospital cat_nohospital

gen cat_noinpatient = .
replace cat_noinpatient = 1 if podes_noinpatientaccess ==0
replace cat_noinpatient = 2 if podes_noinpatientaccess >0 & podes_noinpatientaccess <=5
replace cat_noinpatient = 3 if podes_noinpatientaccess >5 & podes_noinpatientaccess <=20
replace cat_noinpatient = 4 if podes_noinpatientaccess >20 & podes_noinpatientaccess <=50
replace cat_noinpatient = 5 if podes_noinpatientaccess >50 & podes_noinpatientaccess !=.
lab var cat_noinpatient "cat % of villages in region with no easy access to inpatient facilities"
lab def cat_noinpatient 1 "0" 2 "up to 5%" 3 ">5 to 20%" 4 ">20 to 50%" 5 ">50%"
lab val cat_noinpatient cat_nohospital

gen cat_pctgood_use = .
replace cat_pctgood_use = 1 if podes_access_binary >0 & podes_access_binary <=50
replace cat_pctgood_use = 2 if podes_access_binary >50 & podes_access_binary <=70
replace cat_pctgood_use = 3 if podes_access_binary >70 & podes_access_binary <=95
replace cat_pctgood_use = 4 if podes_access_binary >95 & podes_access_binary <100
replace cat_pctgood_use = 5 if podes_access_binary ==100 & podes_access_binary !=.
lab var cat_pctgood_use "cat % of villages in region with access to hospital or 4+ health services"
lab def cat_pctgood 1 "<=50%" 2 ">50% to 70%" 3 ">70 to 95%" 4 ">95 to <100%" 5 "100%"
lab val cat_pctgood_use cat_pctgood

gen cat_pctgood_pay = .
replace cat_pctgood_pay = 1 if podes_pay_binary >0 & podes_pay_binary <=50
replace cat_pctgood_pay = 2 if podes_pay_binary >50 & podes_pay_binary <=70
replace cat_pctgood_pay = 3 if podes_pay_binary >70 & podes_pay_binary <=95
replace cat_pctgood_pay = 4 if podes_pay_binary >95 & podes_pay_binary <100
replace cat_pctgood_pay = 5 if podes_pay_binary ==100 & podes_pay_binary !=.
lab var cat_pctgood_pay "cat % of villages in region w/ access to hospital or 4+ health services where payment is likely"
lab val cat_pctgood_pay cat_pctgood

gen cat_pctzero_use = .
replace cat_pctzero_use = 1 if podes_zero_access_binary ==0
replace cat_pctzero_use = 2 if podes_zero_access_binary >0 & podes_zero_access_binary <=5
replace cat_pctzero_use = 3 if podes_zero_access_binary >5 & podes_zero_access_binary <=20
replace cat_pctzero_use = 4 if podes_zero_access_binary >20 & podes_zero_access_binary <=50
replace cat_pctzero_use = 5 if podes_zero_access_binary >50 & podes_zero_access_binary !=.
lab var cat_pctzero_use "cat % of villages in region with no easy access to any health services"
lab val cat_pctzero_use cat_nohospital

format podes* %9.1f

*NOW MERGE IN TOTAL NUMBERS OF HEALTH FACILITIES
merge 1:1 region year using "$tempfiles/podesmerge_reg_total_faskes_temp.dta"
drop _merge
save "analysis_data/2018/region/podes_reg.dta", replace



************************************
*SECTION D: 
*BPJS DATA MANAGEMENT 
************************************

clear
insheet using "$master/claim_bpjs.csv", comma
save "$tempfiles/claim_bpjs.dta", replace
clear
insheet using "$master/peserta_bpjs_reassigned.csv", comma
save "$tempfiles/peserta_bpjs.dta", replace

merge 1:1 district year using "$tempfiles/claim_bpjs.dta"
drop _merge


***************************************************************************************
*BPJS 2017 VALUES ARE AS AT  JAN 1 2018; REASSIGNING FOR YEAR MERGES
recode year 2017=2018
keep if year ==2018
***************************************************************************************

*RENAME VARIABLES
**rename  bpjs_claim_total
rename total_peserta bpjs_total_insured
*rename claim_in bpjs_claim_in
*rename claim_out bpjs_claim_out
lab var bpjs_claim_total "total claims paid by bpjs"
lab var bpjs_claim_out "outpatient claims paid by bpjs"
lab var bpjs_claim_in "inpatient claims paid by bpjs"
rename ppu bpjs_formal
lab var bpjs_formal "bpjs-registered formal sector workers, incl civil servants"
rename pbpu bpjs_informal
lab var bpjs_informal "bpjs-registered informal sector workers"
rename bp bpjs_non_working
lab var bpjs_non_working "bpjs-registered pensioners, widows, small biz owners"
rename pbi bpjs_subsidised
lab var bpjs_subsidised "bpjs-registered, premimum paid by government"
lab var bpjs_total_insured "total insurance participants registered with bpjs"
lab var bpjs_cases_in "number of inpatient claims"
lab var bpjs_cases_out "number of outpatient claims"
lab var bpjs_cases_total "total number of bpjs insurance claims"

*INFLATE NUMBERS BY AVERAGE QUARTERLY GROWTH FOR 2017, TO REFLECT ESTIMATED NUMBER AT MARCH 2018
local peserta bpjs_total_insured bpjs_formal bpjs_informal bpjs_non_working bpjs_subsidised
foreach x of local peserta {
local suffix _infl
local lbl : var label `x'
gen `x'`suffix' = (`x'*1.023)
lab var `x'`suffix'  "`lbl", estimated at end-March 2018'"
}
format *_infl %15.0fc



save "$tempfiles/bpjs_all_temp2.dta", replace


clear
insheet using "$master/kabkota_2016.csv", comma
destring district, replace
gen kab_name=lower(nama_kab)
gen district_n = district
labmask district_n, val(kab_name)
save "$tempfiles/kabkota_2016.dta", replace
merge 1:m district using "$tempfiles/bpjs_all_temp2.dta"
drop _merge
save "$tempfiles/bpjs_totalraw.dta", replace

************************
*SECTION D1: 
*BPJS FOR KABUPATEN LEVEL
************************


*MANAGE DATA WITH BPJS AT KABUPATEN LEVEL 
clear 
use "$tempfiles/bpjs_totalraw.dta"

*GEN NEW VARIABLES
*USE LOOP:  MAKE PER CAPITA VALUES FOR CLAIMS DATA, VALUE 

local all_bpjs_vars bpjs_claim_in bpjs_claim_out bpjs_claim_total
foreach x of local all_bpjs_vars {
local suffix _pc
gen `x'`suffix' = (`x'*1000/bpjs_total_insured)
}
lab var bpjs_claim_in_pc "bpjs inpatient claims per 1000 registered insured (value)"
lab var bpjs_claim_out_pc "bpjs outpatient claims per 1000 registered insured (value)"
lab var bpjs_claim_total_pc "bpjs total claims per 1000 registered insured (value)"


*CALCULATE AVERAGE COST TO BPJS PER CLAIM 
gen bpjs_perclaim_in = bpjs_claim_in/bpjs_cases_in
gen bpjs_perclaim_out = bpjs_claim_out/bpjs_cases_out
lab var bpjs_perclaim_in "Average spending per inpatient claim"
lab var bpjs_perclaim_out "Average spending per outpatient claim"

format bpjs_perclaim* %12.0fc

*CALCULATE AVERAGE CLAIMS PER 1000 PARTICIPANTS
local all_bpjs_cases bpjs_cases_in bpjs_cases_out bpjs_cases_total
foreach x of local all_bpjs_cases {
local suffix _pc
gen `x'`suffix' = ((`x'*1000)/bpjs_total_insured)
}

lab var bpjs_cases_in_pc "number bpjs inpatient claims per 1000 registered insured"
lab var bpjs_cases_out_pc "number bpjs outpatient claims per 1000 registered insured"
lab var bpjs_cases_total_pc "number bpjs total claims per 1000 registered insured"

gen region = .
replace region = 1 if province >=30 & province <=51
replace region = 2 if province <30
replace region = 3 if (province >51 & province <80) & province !=53
replace region = 4 if (province == 53 | province >=80)
lab var region "region of Indonesia"
lab def region 1 "Java and Bali" 2 "Sumatera & islands" 3 "NTB, Kalimantan, Sulawesi" 4 "NTT, Malukus, Papuas"
lab val region region 

***********
*MERGE IN POP DATA FOR KABUPATEN PER CAPITAS
***********

merge 1:1 district year using "analysis_data/2018/district/popdata_kab.dta", keep (match master)
drop _merge

gen pct_bpjs_insured = (bpjs_total_insured/population)*100
lab var pct_bpjs_insured "Pct of actual population insured, according to BPJS"
gen pct_bpjs_insured_infl = (bpjs_total_insured_infl/population)*100
lab var pct_bpjs_insured_infl "Pct of actual population insured, according to BPJS, estimate at March 2018"

format bpjs* %18.0fc
format pct_bpjs* %9.1f
 

*USE LOOP:  MAKE PER CAPITA VALUES FOR CLAIMS DATA

*GENERATE REAL PER 000 CAPITA VARIABLES
local all_bpjs_vars bpjs_claim_in bpjs_claim_out bpjs_claim_total 
foreach x of local all_bpjs_vars {
local suffix _realpc
gen `x'`suffix' = (`x'*1000/population)
}

lab var bpjs_claim_in_realpc "bpjs inpatient claims per 1,000 (incl uninsured)"
lab var bpjs_claim_out_realpc "bpjs outpatient claims per 1,000 (incl uninsured)"
lab var bpjs_claim_total_realpc "bpjs total claims per 1,000 (incl uninsured)"


*CALCULATE NUMBER OF CLAIMS PER 1000 POPULATION
local all_bpjs_cases bpjs_cases_in bpjs_cases_out bpjs_cases_total
foreach x of local all_bpjs_cases {
local suffix _realpc
gen `x'`suffix' = ((`x'*1000)/population)
}

lab var bpjs_cases_in_realpc "number bpjs inpatient claims per 1000 population"
lab var bpjs_cases_out_realpc "number bpjs outpatient claims per 1000 population"
lab var bpjs_cases_total_realpc "number bpjs total claims per 1000 population"

local convert bpjs_claim_in bpjs_claim_in_pc bpjs_claim_in_realpc bpjs_claim_out bpjs_claim_out_pc bpjs_claim_out_realpc bpjs_perclaim_in bpjs_perclaim_out bpjs_claim_total bpjs_claim_total_pc bpjs_claim_total_realpc
foreach x of local convert {
	gen usd_`x' = (`x'/13384)
	local lbl : var label `x'
	lab var usd_`x'  "US$ `lbl', av 2017 exchange rate'"
}
format usd_* %15.2fc


save "analysis_data/2018/district/bpjs_kab.dta",replace

*********************
*MANAGE BPJS FOR MERGE INTO INDIVIDUAL DATA: SAVE PERCAPITA VALUES ONLY
*********************
use "analysis_data/2018/district/bpjs_kab.dta", clear
keep district district_n year *_pc *_realpc 
save "$tempfiles/bpjs_per_capita_for_monster_merge.dta", replace


************************
*SECTION D2: 
*BPJS FOR PROVINCE LEVEL
************************

clear
use "analysis_data/2018/district/bpjs_kab.dta",replace

save "$tempfiles/bpjs_prov_temp.dta", replace
drop *pc *perclaim*
*SAVE OLD VARIABLE LABELS
foreach v of var * {
local l`v' : variable label `v'
if `"`l`v''"' == "" {
local l`v' "`v'"
}
}
*COLLAPSE
collapse (sum) bpjs*, by (year province)
*RESTORE OLD VARIABLE LABELS
foreach v of var * {
label var `v' "`l`v''"
}
save "$tempfiles/bpjs_prov_temp.dta", replace
***********
*MERGE IN POP DATA FOR PROV PER CAPITAS
***********
use "$tempfiles/bpjs_prov_temp.dta",replace
merge 1:1 province year using "analysis_data/2018/province/popdata_prov.dta" 
drop _merge
provshort
*GEN NEW VARIABLES
*USE LOOP:  MAKE PER CAPITA VALUES FOR CLAIMS DATA
local all_bpjs_vars bpjs_claim_in bpjs_claim_out bpjs_claim_total 
foreach x of local all_bpjs_vars {
local suffix _pc
gen `x'`suffix' = (`x'*1000/bpjs_total_insured)
}
lab var bpjs_claim_in_pc "bpjs inpatient claims per 1000 registered insured"
lab var bpjs_claim_out_pc "bpjs outpatient claims per 1000 registered insured"
lab var bpjs_claim_total_pc "bpjs total claims per 1000 registered insured"



*CALCULATE AVERAGE COST TO BPJS PER CLAIM 
gen bpjs_perclaim_in = bpjs_claim_in/bpjs_cases_in
gen bpjs_perclaim_out = bpjs_claim_out/bpjs_cases_out
lab var bpjs_perclaim_in "Average spending per inpatient claim"
lab var bpjs_perclaim_out "Average spending per outpatient claim"

format bpjs_perclaim* %12.0fc

*CALCULATE AVERAGE CLAIMS PER 1000 PARTICIPANTS
local all_bpjs_cases bpjs_cases_in bpjs_cases_out bpjs_cases_total
foreach x of local all_bpjs_cases {
local suffix _pc
gen `x'`suffix' = ((`x'*1000)/bpjs_total_insured)
}

lab var bpjs_cases_in_pc "number bpjs inpatient claims per 1000 registered insured"
lab var bpjs_cases_out_pc "number bpjs outpatient claims per 1000 registered insured"
lab var bpjs_cases_total_pc "number bpjs total claims per 1000 registered insured"


*GENERATE REAL PER CAPITA VARIABLES
local all_bpjs_vars bpjs_claim_in bpjs_claim_out bpjs_claim_total 
foreach x of local all_bpjs_vars {
local suffix _realpc
gen `x'`suffix' = (`x'*1000/population)
}

lab var bpjs_claim_in_realpc "bpjs inpatient claims per 1000 (incl uninsured)"
lab var bpjs_claim_out_realpc "bpjs outpatient claims per 1000 (incl uninsured)"
lab var bpjs_claim_total_realpc "bpjs total claims per 1000 (incl uninsured)"


gen pct_bpjs_insured = (bpjs_total_insured/population)*100
lab var pct_bpjs_insured "Pct of actual population insured, according to BPJS"
gen pct_bpjs_insured_infl = (bpjs_total_insured_infl/population)*100
lab var pct_bpjs_insured_infl "Pct of actual population insured, according to BPJS, estimate at March 2018"

format bpjs* %18.0fc
format pct_bpjs* %9.1f



*CALCULATE NUMBER OF CLAIMS PER 1000 POPULATION
local all_bpjs_cases bpjs_cases_in bpjs_cases_out bpjs_cases_total
foreach x of local all_bpjs_cases {
local suffix _realpc
gen `x'`suffix' = ((`x'*1000)/population)
}

lab var bpjs_cases_in_realpc "number bpjs inpatient claims per 1000 population"
lab var bpjs_cases_out_realpc "number bpjs outpatient claims per 1000 population"
lab var bpjs_cases_total_realpc "number bpjs total claims per 1000 population"


local convert bpjs_claim_in bpjs_claim_in_pc bpjs_claim_in_realpc bpjs_claim_out bpjs_claim_out_pc bpjs_claim_out_realpc bpjs_perclaim_in bpjs_perclaim_out bpjs_claim_total bpjs_claim_total_pc bpjs_claim_total_realpc
foreach x of local convert {
	gen usd_`x' = (`x'/13384)
	local lbl : var label `x'
	lab var usd_`x'  "US$ `lbl', av 2017 exchange rate'"
}
format usd_* %15.2fc



save "analysis_data/2018/province/bpjs_prov.dta",replace


************************
*SECTION D3: 
*BPJS FOR REGIONAL LEVEL
************************
clear
use "analysis_data/2018/district/bpjs_kab.dta",replace
save "$tempfiles/bpjs_reg_temp.dta", replace
drop *pc *perclaim*
*SAVE OLD VARIABLE LABELS
foreach v of var * {
local l`v' : variable label `v'
if `"`l`v''"' == "" {
local l`v' "`v'"
}
}
*COLLAPSE
collapse (sum) bpjs*, by (year region)
*RESTORE OLD VARIABLE LABELS
foreach v of var * {
label var `v' "`l`v''"
}
save "$tempfiles/bpjs_reg_temp.dta", replace
***********
*MERGE IN POPULATION DATA FOR REGIONAL PER CAPITAS
***********
use "$tempfiles/bpjs_reg_temp.dta",clear
merge 1:1 region year using "analysis_data/2018/region/popdata_reg.dta" 
drop _merge
*GEN NEW VARIABLES
*USE LOOP:  MAKE PER CAPITA VALUES FOR CLAIMS DATA
local all_bpjs_vars bpjs_claim_in bpjs_claim_out bpjs_claim_total 
foreach x of local all_bpjs_vars {
local suffix _pc
gen `x'`suffix' = (`x'*1000/bpjs_total_insured)
}
lab var bpjs_claim_in_pc "bpjs inpatient claims per 1000 registered insured"
lab var bpjs_claim_out_pc "bpjs outpatient claims per 1000 registered insured"
lab var bpjs_claim_total_pc "bpjs total claims per 1000 registered insured"

*CALCULATE AVERAGE COST TO BPJS PER CLAIM 
gen bpjs_perclaim_in = bpjs_claim_in/bpjs_cases_in
gen bpjs_perclaim_out = bpjs_claim_out/bpjs_cases_out
lab var bpjs_perclaim_in "Average spending per inpatient claim"
lab var bpjs_perclaim_out "Average spending per outpatient claim"

format bpjs_perclaim* %12.0fc

*CALCULATE AVERAGE CLAIMS PER 1000 PARTICIPANTS
local all_bpjs_cases bpjs_cases_in bpjs_cases_out bpjs_cases_total
foreach x of local all_bpjs_cases {
local suffix _pc
gen `x'`suffix' = ((`x'*1000)/bpjs_total_insured)
}

lab var bpjs_cases_in_pc "number bpjs inpatient claims per 1000 registered insured"
lab var bpjs_cases_out_pc "number bpjs outpatient claims per 1000 registered insured"
lab var bpjs_cases_total_pc "number bpjs total claims per 1000 registered insured"


*GENERATE REAL PER 1000 CAPITA VARIABLES
local all_bpjs_vars bpjs_claim_in bpjs_claim_out bpjs_claim_total 
foreach x of local all_bpjs_vars {
local suffix _realpc
gen `x'`suffix' = (`x'*1000/population)
}

lab var bpjs_claim_in_realpc "bpjs inpatient claims per 1000 (incl uninsured)"
lab var bpjs_claim_out_realpc "bpjs outpatient claims per 1000 (incl uninsured)"
lab var bpjs_claim_total_realpc "bpjs total claims per 1000 (incl uninsured)"

gen pct_bpjs_insured = (bpjs_total_insured/population)*100
lab var pct_bpjs_insured "Pct of actual population insured, according to BPJS"
gen pct_bpjs_insured_infl = (bpjs_total_insured_infl/population)*100
lab var pct_bpjs_insured_infl "Pct of actual population insured, according to BPJS, estimate at March 2018"

format bpjs* %18.0fc
format pct_bpjs* %9.1f



*CALCULATE NUMBER OF CLAIMS PER 1000 POPULATION
local all_bpjs_cases bpjs_cases_in bpjs_cases_out bpjs_cases_total
foreach x of local all_bpjs_cases {
local suffix _realpc
gen `x'`suffix' = ((`x'*1000)/population)
}

lab var bpjs_cases_in_realpc "number bpjs inpatient claims per 1000 population"
lab var bpjs_cases_out_realpc "number bpjs outpatient claims per 1000 population"
lab var bpjs_cases_total_realpc "number bpjs total claims per 1000 population"



local convert bpjs_claim_in bpjs_claim_in_pc bpjs_claim_in_realpc bpjs_claim_out bpjs_claim_out_pc bpjs_claim_out_realpc bpjs_perclaim_in bpjs_perclaim_out bpjs_claim_total bpjs_claim_total_pc bpjs_claim_total_realpc
foreach x of local convert {
	gen usd_`x' = (`x'/13384)
	local lbl : var label `x'
	lab var usd_`x'  "US$ `lbl', av 2017 exchange rate'"
}
format usd_* %15.2fc


save "analysis_data/2018/region/bpjs_reg.dta",replace
erase "$tempfiles/bpjs_reg_temp.dta"
erase "$tempfiles/bpjs_prov_temp.dta"


*********************
*SECTION E:
*IPKM FOR MERGE
*********************
clear  
insheet using "$master/IPKM_subindex.csv", comma
keep province district ipkm_min_access_2018
gen year = 2018
rename ipkm_min_access_2018 ipkm
lab var ipkm "MoH index of health status"
replace ipkm = ipkm*100
gen ipkm_healthneed = 100- ipkm
lab var ipkm_healthneed "MoH index of health need (100-health status)"
xtile cat_healthneed = ipkm_healthneed, nquantiles(5)
lab var cat_healthneed "district health needs index, excluding access, in quintiles"
lab def cat_healthneed 1 "1 highest health status" 2 "2nd best health" 3 "middle health status" 4 "2nd worst health" 5 "5 lowest health status"
lab val cat_healthneed cat_healthneed

format ipkm* %9.1f
save "analysis_data/2018/district/ipkm_kab.dta", replace

*********************
*MAKE PROVINCIAL IPKM DATASET
*********************
clear
use "analysis_data/2018/district/ipkm_kab.dta", clear
*SAVE OLD VARIABLE LABELS
foreach v of var * {
local l`v' : variable label `v'
if `"`l`v''"' == "" {
local l`v' "`v'"
}
}
collapse (mean) ipkm*, by (year province)
foreach v of var * {
label var `v' "`l`v''"
}
xtile cat_healthneed = ipkm_healthneed, nquantiles(5)
lab var cat_healthneed "district health needs index, excluding access, in quintiles"
lab val cat_healthneed cat_healthneed




save "analysis_data/2018/province/ipkm_prov.dta", replace


*********************
*MAKE REGIONAL IPKM DATASET
*********************
clear
use "analysis_data/2018/district/ipkm_kab.dta", clear
gen region = .
replace region = 1 if province >=30 & province <=51
replace region = 2 if province <30
replace region = 3 if (province >51 & province <80) & province !=53
replace region = 4 if (province == 53 | province >=80)
lab var region "region of Indonesia"
lab def region 1 "Java and Bali" 2 "Sumatera & islands" 3 "NTB, Kalimantan, Sulawesi" 4 "NTT, Malukus, Papuas"
lab val region region 

*SAVE OLD VARIABLE LABELS
foreach v of var * {
local l`v' : variable label `v'
if `"`l`v''"' == "" {
local l`v' "`v'"
}
}
collapse (mean) ipkm*, by (year region)
foreach v of var * {
label var `v' "`l`v''"
}

save "analysis_data/2018/region/ipkm_reg.dta", replace

******************
*SECTION EMAPS: 
*MANAGE DATA FOR MAPPING MERGES
******************
/*
*INSTALL MAPPING COMMANDS IF NECESSARY
*ssc install spmap
*ssc install shp2dta
*ssc install mif2dta


*THE SHAPE FILES ALSO SEEM TO LEAVE BLANKS IN THREE DISTRICTS IN PAPUA/PAPUA BARAT, THOUGH FOR 2018 ALL CODES MATCH
*FOR PODES, MAPPING _D FILES AND KAB MERGED FILE
*IN CASE OF CORRUPTION, DELETE MAP FILES AND RE-RUN NEXT 7 LINES. IF YOU WANT TO USE UNDROPPED VERSION, REMOVE DROP LINE

shp2dta using "$master/idn_bnd_adm2_2017_bps_a/idn_bnd_adm2_2017_bps_a", database("maps/district2017_d") coordinates("maps/district2017_c") genid(id)
shp2dta using "$master/idn_bnd_adm1_2017_bps_a/idn_bnd_adm1_2017_bps_a", database("maps/prov2017_d") coordinates("maps/prov2017_c") genid(id)

use "maps/prov2017_d.dta", clear
gen province = A1C_2013
gen provname = lower(A1N_2013)
keep province provname id 
save "maps/prov2017_d.dta", replace

use "maps/district2017_d", clear
gen province = A1C_BPS
gen district = A2C_BPS
gen district_name = lower(A2N_BPS)
keep id district district_name
save "maps/district2017_d.dta", replace


*/

*NOTES ON MERGING OF FILES

*DATASETS MUST BE MERGED AT DIFFERENT LEVELS BECAUSE OF WEIGHTING, WHICH SHOULD OPERATE ON SOME BUT NOT
*ALL OF THE VARIABLES WHICH WILL BE COLLAPSED. FOR THE PURPOSES OF MULTIVARIATE REGRESSION ON THE HOUSEHOLD LEVEL DATA
*I'VE MERGED INTO THE SUSENAS DATA THE KABUPATEN LEVEL VARIABLES THAT ARE COMPARABLE: IPKM, PODES PERCENTAGES AND PER CAPITA BPJS DATA.

*FOR THE MAPPING AND THE ANALYSIS AT SPECIFIC LEVELS, I HAVE IMPORTED THE DATA THAT SHOULD NOT BE WEIGHTED AFTER THE RELEVANT COLLAPSE
*E.G. COLLAPSE SUSENAS TO KABUPATEN, THEN MERGE WITH KABUPATEN LEVEL DATA FOR OTHER DATASETS.
*DITTO FOR PROVINCE AND REGION. 

*****************
*SECTION F: 
*MERGE KABUPATEN AVERAGES FOR IPKM, AND BPJS PER CAPITA INTO INDIVIDUAL SUSENAS RECORDS
*THIS IS USEFUL FOR MULTIVARIATE, BUT CAN'T BE USED FOR COLLAPSE BECAUSE WEIGHTINGS ARE DIFFERENT
*****************

*/

use "analysis_data/2018/hh/susenas_2018.dta", clear

*MERGE WITH IPKM
merge m:1 district year using "analysis_data/2018/district/ipkm_kab.dta"
drop _merge

*MERGE WITH BPJS PERCAPITA DATA
merge m:1 district year using "$tempfiles/bpjs_per_capita_for_monster_merge.dta"
drop _merge

*MERGE WITH PODES DATA
merge m:1 district year using "analysis_data/2018/district/podes_kab.dta"
drop _merge
save "analysis_data/2018/hh/susenas_ipkm_podes_bpjs_individual.dta", replace

*****************
*SECTION G1: 
*KABUPATEN ALL MERGED DATASET FOR ANALYSIS
*****************

use "analysis_data/2018/hh/susenas_2018.dta", clear

*DEFINE VARIABLES FOR DIFFERENT TYPES OF COLLAPSE
*BEACUSE OF SIGNIFICANT SKEW, HEALTH SPENDING VARIABLES ARE MEDIANS; US$ CONVERSIONS RE-RUN ON MEDIAN RUPIAH VALUES


*SAVE OLD VARIABLE LABELS
foreach v of var * {
local l`v' : variable label `v'
if `"`l`v''"' == "" {
local l`v' "`v'"
}
}

local collapse_median health* outpatient_ave1mo outpatient_ave1mo_cap outpatient_spend_hh exp* inpatient_ave1yr_realcap
local collapse_mean any* poor impoverish cat* symptoms_1mo outpatient_1mo insur* inpatient_1yr treat* 



*COLLAPSE
collapse (mean) `collapse_mean' (p50) `collapse_median' [pweight=wert] , by(year district)

drop treat_inpatient_where treat_whynot_op
gen inpatient_ave1yr_cap_all = inpatient_ave1yr_realcap
recode inpatient_ave1yr_cap_all .=0


*RESTORE OLD VARIABLE LABELS
foreach v of var * {
label var `v' "`l`v''"
}

global make_pct poor impoverish symptoms_1mo outpatient_1mo insurance ///
anybpjs_pbi anybpjs_nonpbi anyjamkesda anyswasta anycompany any_company_or_private anyoop ///
insurance_jkn_all insurance_jkn_insured insurancenumber insurancenumber ///
insurance_use_last_op insurance_use_last_ip insurance_jkn_used insurance_jkn_used_ip ///
inpatient_1yr treat_outpatient treated_op
/* treat_self  treat_ip_rsu treat_ip_puskesmas treat_ip_private ///
treat_ip_other treat_ip_multiple refused_ip_paperwork refused_ip_notscreened ///
refused_ip_notcovered refused_ip_nostaff refused_ip_noroom refused_ip_nomeds ///
refused_ip_late refused_ip refused_hc_paperwork refused_hc_notscreened ///
refused_hc_notcovered refused_hc_nostaff refused_hc_nomeds refused_hc_late refused_hc ///
nu_hc_notsick nu_hc_selftreat nu_hc_complicated nu_hc_inactive nu_hc_toofar nu_hc_unstaffed ///
nu_hc_nomoney nu_hc_longwait nu_hc_otherinsurance nu_hc_multiple nu_ip_complicated ///
nu_ip_inactive nu_ip_toofar nu_ip_unstaffed nu_ip_nomoney nu_ip_longwait ///
nu_ip_otherinsurance nu_ip_multiple
*/

foreach x of global make_pct {
replace `x' = (`x'*100)
}
format $make_pct %9.1f

save "$tempfiles/susenas_collapsed_kab.dta", replace

merge 1:1 district year using "analysis_data/2018/district/popdata_kab.dta"
drop _merge
save "$tempfiles/susenas_collapsed_kab_temp1.dta", replace

*MERGE WITH IPKM
use "$tempfiles/susenas_collapsed_kab_temp1.dta", clear
merge 1:1 district year using "analysis_data/2018/district/ipkm_kab.dta"
drop _merge
save "$tempfiles/susenas_collapsed_kab_temp2.dta", replace
*MERGE WITH BPJS
clear
use "$tempfiles/susenas_collapsed_kab_temp2.dta", clear
merge 1:1 district year using "analysis_data/2018/district/bpjs_kab.dta"
drop _merge
save "$tempfiles/susenas_collapsed_kab_temp3.dta", replace
*MERGE WITH PODES
use "$tempfiles/susenas_collapsed_kab_temp3.dta", clear
merge 1:1 district year using "analysis_data/2018/district/podes_kab.dta"
drop _merge
format  bpjs* `collapse_median' %15.0fc
format `collapse_mean' %9.1f

*USD CONVERSIONS FOR COLLAPSED DATA

drop usd_*

local convert bpjs_claim_in bpjs_claim_in_pc bpjs_claim_in_realpc bpjs_claim_out bpjs_claim_out_pc bpjs_claim_out_realpc bpjs_perclaim_in bpjs_perclaim_out bpjs_claim_total bpjs_claim_total_pc bpjs_claim_total_realpc
foreach x of local convert {
	gen usd_`x' = (`x'/13384)
	local lbl : var label `x'
	lab var usd_`x'  "US$ `lbl', av 2017 exchange rate'"
}

local expenditure health_exp_1yr_hh health_exp_1yr_hh_cap health_exp_1yr_private inpatient_ave1yr_realcap inpatient_ave1yr_cap_all outpatient_ave1mo outpatient_ave1mo_cap
foreach x of local expenditure {
	gen usd_`x' = (`x'/13760)
	local lbl : var label `x'
	lab var usd_`x'  "US$ `lbl', at March 2018 exchange rate'"
}
format usd_* %9.2fc


*ADD PER CAPITA MEASURES FOR FASKES

*GENERATE PER 10,000 CAPITA INPATIENT SERVICES (HOSPITALS/PUSKESMAS WITH RI)
gen podes_inpatient_pc = (podes_total_hospital+ podes_total_phcinpatient)*100000/population
lab var podes_inpatient_pc "number of hospitals or health centres with inpatients, per 100,000 population"
gen podes_hospital_pc = (podes_total_hospital)*100000/population
lab var podes_hospital_pc "number of hospitals per 100,000 population"

*GENERATE PER 10,000 CAPITA PHARMACIES (NORMAL, PLUS THOSE IN HOSPITALS)
gen podes_buymeds_pc = (podes_total_hospital+ podes_total_pharmacy)*100000/population
gen podes_pharmacy_pc = podes_total_pharmacy*100000/population
lab var podes_buymeds_pc "number of pharmacies or hospitals (w pharmacies), per 100,000 population"
lab var podes_pharmacy_pc "number of pharmacies, per 100,000 population"
format podes*pc %9.2f

nameprovs 
provshort
gen citystr= string(district)
gen city=0 
replace city = 1 if substr(citystr,3,1) == "7"
drop citystr
lab var city "kota, not kabupaten"
lab def city 0 "Rural" 1 "City"
lab val city city

xtile cat_insurance = insurance, nquantiles(5)
lab var cat_insurance "% insurance coverage in district, in quintiles"
lab def cat_insurance 1 "1 lowest insurance quintile" 2 "2nd least insured" 3 "mid insurance quintile" 4 "2nd most insured" 5 "5 highest insurance quintile"
lab val cat_insurance cat_insurance



save "analysis_data/2018/district/all_nomaps_kab", replace
keep district year podes_*_pc

*MERGE NEW PC VARIABLES BACK INTO INDIVIDUAL FILES
merge 1:m district year using "analysis_data/2018/hh/susenas_ipkm_podes_bpjs_individual.dta"
drop _merge
save "analysis_data/2018/hh/susenas_ipkm_podes_bpjs_individual.dta", replace

*****************
*SECTION G1a: 
*KABUPATEN FOR MAPPING, 2018
*****************


use "analysis_data/2018/district/all_nomaps_kab", clear
keep if year == 2018

merge 1:1 district using "maps/district2017_d", replace update
drop if _merge==2
drop _merge
save "analysis_data/2018/district/2018_mapping_kab", replace

*ALSO MAKE ONLY PODES DATASET WITH MAPPING AT KABUPATEN LEVEL
use "analysis_data/2018/district/podes_kab", clear
keep if year == 2018
merge 1:m district using "maps/district2017_d", replace update
drop if _merge==2
save "analysis_data/2018/district/podes_kab_mapping", replace



*****************
*SECTION G2: 
*PROVINCE ALL MERGED DATASET FOR MAPPING AND ANALYSIS
*****************

use "analysis_data/2018/hh/susenas_2018.dta", clear

local collapse_median health* outpatient_ave1mo outpatient_ave1mo_cap outpatient_spend_hh exp* inpatient_ave1yr_realcap
local collapse_mean any* poor impoverish cat* symptoms_1mo outpatient_1mo insur* inpatient_1yr treat* 


*SAVE OLD VARIABLE LABELS
foreach v of var * {
local l`v' : variable label `v'
if `"`l`v''"' == "" {
local l`v' "`v'"
}
}


*COLLAPSE
collapse (mean) `collapse_mean' (p50) `collapse_median' [pweight=wert] , by(year province)

drop treat_inpatient_where treat_whynot_op
gen inpatient_ave1yr_cap_all = inpatient_ave1yr_realcap
recode inpatient_ave1yr_cap_all .=0

*RESTORE OLD VARIABLE LABELS
foreach v of var * {
label var `v' "`l`v''"
}

foreach x of global make_pct {
replace `x' = (`x'*100)
}
format $make_pct %9.1f




merge 1:1 province year using "analysis_data/2018/province/popdata_prov.dta"
drop _merge
save "$tempfiles/susenas_collapsed_prov_temp1.dta", replace

*MERGE WITH IPKM
use "$tempfiles/susenas_collapsed_prov_temp1.dta", clear
merge 1:1 province year using "analysis_data/2018/province/ipkm_prov.dta"
drop _merge
save "$tempfiles/susenas_collapsed_prov_temp2.dta", replace
*MERGE WITH BPJS
clear
use "$tempfiles/susenas_collapsed_prov_temp2.dta", clear
merge 1:1 province year using "analysis_data/2018/province/bpjs_prov.dta"
drop _merge
save "$tempfiles/susenas_collapsed_prov_temp3.dta", replace
*MERGE WITH PODES
use "$tempfiles/susenas_collapsed_prov_temp3.dta", clear
merge 1:1 province year using "analysis_data/2018/province/podes_prov.dta"

*USD CONVERSIONS FOR COLLAPSED DATA

drop usd_*

local convert bpjs_claim_in bpjs_claim_in_pc bpjs_claim_in_realpc bpjs_claim_out bpjs_claim_out_pc bpjs_claim_out_realpc bpjs_perclaim_in bpjs_perclaim_out bpjs_claim_total bpjs_claim_total_pc bpjs_claim_total_realpc
foreach x of local convert {
	gen usd_`x' = (`x'/13384)
	local lbl : var label `x'
	lab var usd_`x'  "US$ `lbl', av 2017 exchange rate'"
}

local expenditure health_exp_1yr_hh health_exp_1yr_hh_cap health_exp_1yr_private inpatient_ave1yr_realcap inpatient_ave1yr_cap_all outpatient_ave1mo outpatient_ave1mo_cap
foreach x of local expenditure {
	gen usd_`x' = (`x'/13760)
	local lbl : var label `x'
	lab var usd_`x'  "US$ `lbl', at March 2018 exchange rate'"
}

drop _merge
format health* bpjs* insured_* %15.0fc
format usd_* %9.2fc
format any* cat* symptoms_1mo outpatient* insura* pct* inpatient* poor *impoverish treat_* %9.2f


save "analysis_data/2018/province/all_nomaps_prov", replace

*****************
*SECTION G2a: 
*PROVINCE FOR MAPPING, 2018
*****************

use "analysis_data/2018/province/all_nomaps_prov", clear

keep if year == 2018
merge 1:1 province using "maps/prov2017_d.dta"
drop _merge

save "analysis_data/2018/province/2018_mapping_prov", replace

*****************
*SECTION G3: 
*REGION ALL MERGED DATASET FOR ANALYSIS
*****************
*/
use "analysis_data/2018/hh/susenas_2018.dta", clear
save "$tempfiles/susenas_collapsed_reg.dta", replace

local collapse_median health* outpatient_ave1mo outpatient_ave1mo_cap outpatient_spend_hh exp* inpatient_ave1yr_realcap usd_*
local collapse_mean any* poor impoverish cat* symptoms_1mo outpatient_1mo insur* inpatient_1yr treat* 


*SAVE OLD VARIABLE LABELS
foreach v of var * {
local l`v' : variable label `v'
if `"`l`v''"' == "" {
local l`v' "`v'"
}
}


*COLLAPSE
collapse (mean) `collapse_mean' (p50) `collapse_median' [pweight=wert] , by(year region)

drop treat_inpatient_where treat_whynot_op
gen inpatient_ave1yr_cap_all = inpatient_ave1yr_realcap
recode inpatient_ave1yr_cap_all .=0

*RESTORE OLD VARIABLE LABELS
foreach v of var * {
label var `v' "`l`v''"
}

foreach x of global make_pct {
replace `x' = (`x'*100)
}
format $make_pct %9.1f


merge 1:1 region year using "analysis_data/2018/region/popdata_reg.dta"
drop _merge
save "$tempfiles/susenas_collapsed_reg_temp1.dta", replace

*MERGE WITH IPKM
use "$tempfiles/susenas_collapsed_reg_temp1.dta", clear
merge 1:1 region year using "analysis_data/2018/region/ipkm_reg.dta"
drop _merge
save "$tempfiles/susenas_collapsed_reg_temp2.dta", replace
*MERGE WITH BPJS
clear
use "$tempfiles/susenas_collapsed_reg_temp2.dta", clear
merge 1:1 region year using "analysis_data/2018/region/bpjs_reg.dta"
drop _merge
save "$tempfiles/susenas_collapsed_reg_temp3.dta", replace
*MERGE WITH PODES
use "$tempfiles/susenas_collapsed_reg_temp3.dta", clear
merge 1:1 region year using "analysis_data/2018/region/podes_reg.dta"
drop _merge

*USD CONVERSIONS FOR COLLAPSED DATA

drop usd_*

local convert bpjs_claim_in bpjs_claim_in_pc bpjs_claim_in_realpc bpjs_claim_out bpjs_claim_out_pc bpjs_claim_out_realpc bpjs_perclaim_in bpjs_perclaim_out bpjs_claim_total bpjs_claim_total_pc bpjs_claim_total_realpc
foreach x of local convert {
	gen usd_`x' = (`x'/13384)
	local lbl : var label `x'
	lab var usd_`x'  "US$ `lbl', av 2017 exchange rate'"
}

local expenditure health_exp_1yr_hh health_exp_1yr_hh_cap health_exp_1yr_private inpatient_ave1yr_realcap inpatient_ave1yr_cap_all outpatient_ave1mo outpatient_ave1mo_cap exp_cap
foreach x of local expenditure {
	gen usd_`x' = (`x'/13760)
	local lbl : var label `x'
	lab var usd_`x'  "US$ `lbl', at March 2018 exchange rate'"
}


format health* bpjs* insured_* %15.0fc
format any* cat* symptoms_1mo outpatient* insura* pct* inpatient* poor *impoverish treat_* %9.2f
format usd_* %9.2fc

save "analysis_data/2018/region/all_nomaps_reg", replace



*TIDY UP FILES: REMOVE DIRECTORY OF TEMPORARY FILES
clear
cd $tempfiles

shell rm *.dta
cd ..

